<?php
/*
 * inc-request.php
 * Retral/CSCD494 Winter 2009
 * Eastern Washington University
 *
 * File Description:
 *
 * Admin only page, could possibly be expanded to include pendingCompletion.php
 * functionality. Shows the questions that are in need of approval (list of
 * requests).
 *
 * Other:
 *
 */


/* 
 * Set up $page_name
 * This name will be compared to PAGE_NAME in NAV_URLS table (stored in session) and verify permission for given user.
 */
 
$page_name = "Approve";
$page_name = strtoupper($page_name);

$pm = $_SESSION['pagePermissions'];

// Is user logged in, and has permissions?
if (isset($_SESSION['loggedIn']) && $_SESSION['loggedIn'] !="" && $_SESSION['pagePermissions'][$page_name] == 1)
{
?>
<span class="header-text">Change Requests</span><br>

<?php
	$conn = oci_connect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);

	if(isset($_POST['cr'])) {
		$qid  = $_POST['qid'];
		$qver = $_POST['qver'];
		$cr   = $_POST['cr'];
		$rev  = $_POST['rev'];
		

		//$approval is a class found in sessionstart.php
		$query = $approval->getApprovalQuery($rev, $cr);

		$query .= ' WHERE question_id = :QID AND q_version = :QVER
			AND request_code=:RC';

		
		$stmt = oci_parse($conn, $query);
		oci_bind_by_name($stmt, ":QID", $qid);
		oci_bind_by_name($stmt, ":QVER", $qver);
		oci_bind_by_name($stmt, ":RC", $cr);
		if(!oci_execute($stmt))
			print "<P>Failed to modify Change Request</P>";
								
		//$erra=OCIError($stmt);
		//print "Error: ${erra['code']} ${erra['message']}"; 
		
		
		
		if($rev == $approval->response["approve"]) 
		{
			if($cr != $approval->request["remove"])
			{
				//approve the question
					
				//print "VERSION: ".$version;   DEBUG
				//print "QID: ".$qid;
				//print "QVER: ".$qver;
								
				$query = "UPDATE QUESTION SET q_revision = (Select MAX(q_revision)+1 from question WHERE question_id = :qid) WHERE question_id = :qid AND q_version = :qver";
				$stmt = oci_parse($conn, $query) or die("Parse failed");
				oci_bind_by_name($stmt, ":qid", $qid);
				oci_bind_by_name($stmt, ":qver", $qver);
				if(!oci_execute($stmt)) 
					print "<P>Failed to approve question.</P>";				
					
			} //end approve			
		}
		
		if(($rev == $approval->response["improve"] || $rev == $approval->response["approve"]||$rev == $approval->response["doesnotcompile"]||$rev == $approval->response["incomplete"])&& isset($_POST['questionComment']))  //add comment to he DB
		{
			$userID = $_SESSION['userID'];
			//if(!isset($_POST['questionComment']))
			//	$qComments = "No Comment";
			//else
				$qComments = $_POST['questionComment'];
	
	
			$queryComments = "UPDATE change_request SET comments = :qComments where question_id = :qid and q_version = :qver";	
			$stmt = oci_parse($conn, $queryComments);
			oci_bind_by_name($stmt, ":qid", $qid);
			oci_bind_by_name($stmt, ":qver", $qver);
			oci_bind_by_name($stmt, ":qComments", $qComments);
			
			if(!oci_execute($stmt))
				print "<p>Failed to add comments</p>";
		}
	}


?>


<table border=0 width=100%>
	<tr>
		<td width=200><b><u><a href='?page=request&sort=question'>Question</a></u></b></td>
		<td><b><u><a href='?page=request&sort=request'>Request</a></u></b></td>
		<th width=100><u><a href='?page=request'>Date</a></u></th>
		<th width=150><u>Comment</u></th>
		<th width=70><u>Revision</u></th>
	</tr><?PHP

	$sort = " ORDER BY question.date_modified";

	if(isset($_REQUEST['sort'])) {
		if($_REQUEST['sort'] == "request")
			$sort = " ORDER BY revision_type.type_description";
		else if($_REQUEST['sort'] == "question")
			$sort = " ORDER BY question_content.question_title";
	}

	$query = '
	     SELECT question_content.question_title, change_request.question_id,
	          change_request.q_version, comments, question.date_modified,
	          revision_type.type_description 
	     FROM change_request INNER JOIN revision_type 
	     ON change_request.request_code = revision_type.request_code
        INNER JOIN question 
	     ON question.question_id=change_request.question_id 
	          AND question.q_version=change_request.q_version
	     INNER JOIN question_content 
	     ON question_content.question_content_id=question.question_content_id
		  ';

	$query .= ' WHERE revision_type.rev_type = :RQ';
	$query .= $sort;

	$requestValue = 1;
	$stmtList = oci_parse($conn, $query);
	oci_bind_by_name($stmtList, ':RQ', $requestValue);
	if(!oci_execute($stmtList)) {
		print "<P>Could not find request list</P>";
	}

	while($request = oci_fetch_assoc($stmtList)) {

		print "<tr>\n";
		print "\t\t<td><a href='?page=question&id=".$request['QUESTION_ID'];
		print "&ver=".$request['Q_VERSION']."&qAproval'  name='";
		print $request['QUSETION_TITLE']."'>";
		print $request['QUESTION_TITLE']."</a></td>\n";
		print "\t\t<td>".$request['TYPE_DESCRIPTION']."</td>\n";
		print "\t\t<td>".dateFormat($request['DATE_MODIFIED'])."</td>\n";
		print "\t\t<td>";
		print $request['COMMENTS']."</td>\n";
		print "\t\t<td align='right'>".$request['Q_VERSION']."</td>\n";
		print "\t</tr>";
	}
	print "</table>";
	oci_free_statement($stmt);
	oci_free_statement($stmtList);
	oci_close($conn);

?>
<?php
} //end if logged in
else
{
	echo "Invalid credentials";
	echo "<META http-equiv=\"refresh\" content=\"0;URL=index.php?page=login&ld=0\">";
}	
?>
